package com.genghis.ptas.note.dao.impl;

import com.genghis.core.page.dao.PageDao;
import com.genghis.ptas.note.dao.NoteDao;
import com.genghis.ptas.note.entity.Note;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * Created with IntelliJ IDEA.
 * User: gaoyang
 * To change this template use File | Settings | File Templates.
 */
@Repository("noteDao")
public class NoteDaoImpl extends PageDao implements NoteDao {

    public StringBuilder getNoteSql() {
        return new StringBuilder("SELECT * FROM (SELECT n.*,IFNULL(pn.state,-1) state  FROM note n LEFT JOIN publicity_note pn ON n.id=pn.note_id) n where 1=1 ");
    }

    @Override
    public Note findNoteById(int noteId) {
        StringBuilder sql = getNoteSql();
        sql.append(" AND n.id=?");
        return getJdbcTemplate().queryForObject(sql.toString(), new BeanPropertyRowMapper<Note>(Note.class), noteId);
    }

    @Override
    public int addNote(Note note) {
        String sql = "INSERT INTO note(note_content,create_date,note_name,note_type,user_id) " +
                "VALUES(:noteContent, :createDate, :noteName, :noteType, :userId)";
        KeyHolder keyHolder = new GeneratedKeyHolder();
        getNamedParameterJdbcTemplate().update(sql, new BeanPropertySqlParameterSource(note), keyHolder);
        return keyHolder.getKey().intValue();
    }

    @Override
    public void editNote(Note note) {
        String sql = "UPDATE note SET note_content = :noteContent, create_date = :createDate, " +
                "note_name = :noteName, note_type = :noteType, user_id = :userId WHERE id = :id ";
        getNamedParameterJdbcTemplate().update(sql, new BeanPropertySqlParameterSource(note));
    }

    @Override
    public void delNote(String noteId) {
        String sql = "DELETE FROM note WHERE id in(" + noteId + ")";
        getJdbcTemplate().update(sql);
    }

    @Override
    public List<Note> findNoteByType(Note note) {
        StringBuilder sql = getNoteSql();
        if (note.getUserId() != 0) {
            sql.append(" AND n.user_id=").append(note.getUserId());
        }
        if (note.getNoteType() != 0) {
            sql.append(" AND n.note_type=").append(note.getNoteType());
        }
        if (!"".equals(note.getNoteName()) && null != note.getNoteName()) {
            sql.append(" AND n.note_name like '%").append(note.getNoteName()).append("%'");
        }
        if (!"".equals(note.getNoteContent()) && null != note.getNoteContent()) {
            sql.append(" AND n.note_content like '%").append(note.getNoteContent()).append("%'");
        }
        if (!"".equals(note.getFromDate()) && null != note.getFromDate()) {
            sql.append(" AND n.create_date >='").append(note.getFromDate()).append("' ");
        }
        if (!"".equals(note.getToDate()) && null != note.getToDate()) {
            sql.append(" AND n.create_date  <='").append(note.getToDate()).append("' ");
        }
        sql.append(" AND n.note_content!=''");
        sql.append(" order by n.create_date desc");
        return getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<Note>(Note.class));
    }

    @Override
    public Note findNoteByCourseIdAndUserId(Integer courseId, Integer userId) {
        String sql = "SELECT note.*,cr.`course_id` FROM `course_register` cr " +
                "LEFT JOIN `note` ON cr.note_id = note.`id` WHERE cr.`course_id` = ? AND note.`user_id` =" + userId;
        return getJdbcTemplate().queryForObject(sql, new BeanPropertyRowMapper<Note>(Note.class), courseId);
    }

    @Override
    public List<Note> findPublicNote(Note note) {
        StringBuilder sql = getNoteSql();
        if (note.getNoteType() != 0) {
            sql.append(" AND n.note_type=").append(note.getNoteType());
        }
        if (!"".equals(note.getNoteName()) && null != note.getNoteName()) {
            sql.append(" AND n.note_name like '%").append(note.getNoteName()).append("%'");
        }
        if (!"".equals(note.getNoteContent()) && null != note.getNoteContent()) {
            sql.append(" AND n.note_content like '%").append(note.getNoteContent()).append("%'");
        }
        sql.append(" AND n.note_content!='' ");
        sql.append(" AND n.state=1 order by n.create_date desc");
        return getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<Note>(Note.class));
    }

}
